import csv
import datetime
from io import StringIO

from flask import Response
from flask import request, jsonify
from sqlalchemy import text

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_task_models import CompareTableJobResult
from tzq.extensions import db


# 表模型比对结果查询接口
@dcs_bp.route('/api/compareTableJobResults', methods=['GET', 'POST'])
def get_compare_table_job_results():
    export = request.args.get('export')
    if export == '1':
        comp_task_name = request.values.get("comp_task_name")
        schema = request.values.get("schema")
        table_name = request.values.get("table_name")
        column_name = request.values.get("column_name")
        total_check_result = request.values.get("total_check_result")
        table_name_check_result = request.values.get("table_name_check_result")
        column_name_check_result = request.values.get("column_name_check_result")
        data_type_check_result = request.values.get("data_type_check_result")
        data_default_check_result = request.values.get("data_default_check_result")
        pkey_check_result = request.values.get("pkey_check_result")
        nullable_check_result = request.values.get("nullable_check_result")
        table_comment_check_result = request.values.get("table_comment_check_result")
        column_comment_check_result = request.values.get("column_comment_check_result")

        data = CompareTableJobResult.query

        if comp_task_name:
            data = data.filter(CompareTableJobResult.comp_task_name.ilike(f'%{comp_task_name}%'))
        if schema:
            data = data.filter(CompareTableJobResult.schema.ilike(f'%{schema}%'))
        if table_name:
            data = data.filter(CompareTableJobResult.table_name.ilike(f'%{table_name}%'))
        if column_name:
            data = data.filter(CompareTableJobResult.column_name.ilike(f'%{column_name}%'))
        if total_check_result:
            data = data.filter(CompareTableJobResult.total_check_result.ilike(f'%{total_check_result}%'))
        if table_name_check_result:
            data = data.filter(CompareTableJobResult.table_name_check_result.ilike(f'%{table_name_check_result}%'))
        if column_name_check_result:
            data = data.filter(CompareTableJobResult.column_name_check_result.ilike(f'%{column_name_check_result}%'))
        if data_type_check_result:
            data = data.filter(CompareTableJobResult.data_type_check_result.ilike(f'%{data_type_check_result}%'))
        if data_default_check_result:
            data = data.filter(CompareTableJobResult.data_default_check_result.ilike(f'%{data_default_check_result}%'))
        if pkey_check_result:
            data = data.filter(CompareTableJobResult.pkey_check_result.ilike(f'%{pkey_check_result}%'))
        if nullable_check_result:
            data = data.filter(CompareTableJobResult.nullable_check_result.ilike(f'%{nullable_check_result}%'))
        if table_comment_check_result:
            data = data.filter(
                CompareTableJobResult.table_comment_check_result.ilike(f'%{table_comment_check_result}%'))
        if column_comment_check_result:
            data = data.filter(
                CompareTableJobResult.column_comment_check_result.ilike(f'%{column_comment_check_result}%'))

        sort_field = request.args.get('field', 'creation_date')
        sort_order = request.args.get('order', 'desc')
        if hasattr(CompareTableJobResult, sort_field):
            if sort_order == 'asc':
                order_by = getattr(CompareTableJobResult, sort_field).asc()
            else:
                order_by = getattr(CompareTableJobResult, sort_field).desc()
            data = data.order_by(order_by)
        else:
            data = data.order_by(CompareTableJobResult.creation_date.desc())

        data = data.paginate(page=1, per_page=1000000, error_out=False)

        # 创建内存文件对象
        output = StringIO()
        output.write('\ufeff')  # 添加UTF-8 BOM头

        # 创建CSV写入器
        writer = csv.writer(output, quoting=csv.QUOTE_ALL)

        # 写入表头
        headers = [
            'ID', '比对任务名称', 'schema', '表名', '字段名', '汇总',
            '检查表', '表1', '表2', '表3', '表4', '表5', '表6', '表7',
            '检查字段', '字段1', '字段2', '字段3', '字段4', '字段5', '字段6', '字段7',
            '字段类型', '类型1', '类型2', '类型3', '类型4', '类型5', '类型6', '类型7',
            '查默认值', '默认值1', '默认值2', '默认值3', '默认值4', '默认值5', '默认值6', '默认值7',
            '查主键', '主键1', '主键2', '主键3', '主键4', '主键5', '主键6', '主键7',
            '查是空', '是空1', '是空2', '是空3', '是空4', '是空5', '是空6', '是空7',
            '查表注释', '表注释1', '表注释2', '表注释3', '表注释4', '表注释5', '表注释6', '表注释7',
            '查列注释', '列注释1', '列注释2', '列注释3', '列注释4', '列注释5', '列注释6', '列注释7',
            '更新时间'
        ]  # 使用中文表头
        writer.writerow(headers)

        # 写入数据行
        for item in data:
            row = [
                item.result_id,
                item.comp_task_name,
                item.schema,
                item.table_name,
                item.column_name,
                item.total_check_result,
                item.table_name_check_result,
                item.table_name1,
                item.table_name2,
                item.table_name3,
                item.table_name4,
                item.table_name5,
                item.table_name6,
                item.table_name7,
                item.column_name_check_result,
                item.column_name1,
                item.column_name2,
                item.column_name3,
                item.column_name4,
                item.column_name5,
                item.column_name6,
                item.column_name7,
                item.data_type_check_result,
                item.data_type1,
                item.data_type2,
                item.data_type3,
                item.data_type4,
                item.data_type5,
                item.data_type6,
                item.data_type7,
                item.data_default_check_result,
                item.data_default1,
                item.data_default2,
                item.data_default3,
                item.data_default4,
                item.data_default5,
                item.data_default6,
                item.data_default7,
                item.pkey_check_result,
                item.pkey1,
                item.pkey2,
                item.pkey3,
                item.pkey4,
                item.pkey5,
                item.pkey6,
                item.pkey7,
                item.nullable_check_result,
                item.nullable1,
                item.nullable2,
                item.nullable3,
                item.nullable4,
                item.nullable5,
                item.nullable6,
                item.nullable7,
                item.table_comment_check_result,
                item.table_comment1,
                item.table_comment2,
                item.table_comment3,
                item.table_comment4,
                item.table_comment5,
                item.table_comment6,
                item.table_comment7,
                item.column_comment_check_result,
                item.column_comment1,
                item.column_comment2,
                item.column_comment3,
                item.column_comment4,
                item.column_comment5,
                item.column_comment6,
                item.column_comment7,
                item.creation_date,
            ]
            writer.writerow(row)

        date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

        # 创建响应对象
        response = Response(
            output.getvalue(),
            mimetype="text/csv; charset=utf-8",
            headers={
                "Content-disposition": "attachment; filename=" + date_string + "-compare_table_job_result_export.csv",
                "Content-type": "text/csv; charset=utf-8"
            }
        )
        return response
    # 分页请求
    else:
        page = int(request.args.get('page', 1))
        limit = int(request.args.get('limit', 10))

        # 获取搜索参数
        comp_task_name = request.values.get("comp_task_name")
        schema = request.values.get("schema")
        table_name = request.values.get("table_name")
        column_name = request.values.get("column_name")
        total_check_result = request.values.get("total_check_result")
        table_name_check_result = request.values.get("table_name_check_result")
        column_name_check_result = request.values.get("column_name_check_result")
        data_type_check_result = request.values.get("data_type_check_result")
        data_default_check_result = request.values.get("data_default_check_result")
        pkey_check_result = request.values.get("pkey_check_result")
        nullable_check_result = request.values.get("nullable_check_result")
        table_comment_check_result = request.values.get("table_comment_check_result")
        column_comment_check_result = request.values.get("column_comment_check_result")

        # 基础查询
        query = CompareTableJobResult.query

        # 应用搜索条件
        if comp_task_name:
            query = query.filter(CompareTableJobResult.comp_task_name.ilike(f'%{comp_task_name}%'))
        if schema:
            query = query.filter(CompareTableJobResult.schema.ilike(f'%{schema}%'))
        if table_name:
            query = query.filter(CompareTableJobResult.table_name.ilike(f'%{table_name}%'))
        if column_name:
            query = query.filter(CompareTableJobResult.column_name.ilike(f'%{column_name}%'))
        if total_check_result:
            query = query.filter(CompareTableJobResult.total_check_result.ilike(f'%{total_check_result}%'))
        if table_name_check_result:
            query = query.filter(CompareTableJobResult.table_name_check_result.ilike(f'%{table_name_check_result}%'))
        if column_name_check_result:
            query = query.filter(CompareTableJobResult.column_name_check_result.ilike(f'%{column_name_check_result}%'))
        if data_type_check_result:
            query = query.filter(CompareTableJobResult.data_type_check_result.ilike(f'%{data_type_check_result}%'))
        if data_default_check_result:
            query = query.filter(
                CompareTableJobResult.data_default_check_result.ilike(f'%{data_default_check_result}%'))
        if pkey_check_result:
            query = query.filter(CompareTableJobResult.pkey_check_result.ilike(f'%{pkey_check_result}%'))
        if nullable_check_result:
            query = query.filter(CompareTableJobResult.nullable_check_result.ilike(f'%{nullable_check_result}%'))
        if table_comment_check_result:
            query = query.filter(
                CompareTableJobResult.table_comment_check_result.ilike(f'%{table_comment_check_result}%'))
        if column_comment_check_result:
            query = query.filter(
                CompareTableJobResult.column_comment_check_result.ilike(f'%{column_comment_check_result}%'))

        # 获取排序参数
        sort_field = request.args.get('field', 'creation_date')
        sort_order = request.args.get('order', 'desc')

        # 构建排序
        if hasattr(CompareTableJobResult, sort_field):
            if sort_order == 'asc':
                order_by = getattr(CompareTableJobResult, sort_field).asc()
            else:
                order_by = getattr(CompareTableJobResult, sort_field).desc()
            query = query.order_by(order_by)
        else:
            query = query.order_by(CompareTableJobResult.table_info_id.asc())

        # 分页查询
        pagination = query.paginate(page=page, per_page=limit, error_out=False)

        # 构建Layui要求的JSON格式
        return jsonify({
            'code': 0,
            'msg': '',
            'count': pagination.total,
            'data': [tableInfo.to_dict() for tableInfo in pagination.items]
        })


@dcs_bp.route('/api/getCompareTaskNames', methods=['GET'])
def get_compare_task_names():
    """
    从PostgreSQL获取 任务名 下拉框的数据，提供json数据的api接口
    “模型比对”查询页面 的 “任务名”下拉框 展示
    """
    try:
        v_sql = text("""
                     select comp_task_name, creation_date
                     from (select row_number() over(partition by comp_task_name 
             order by creation_date desc) as xh
            ,comp_task_name
                                , creation_date
                           from dc_comp_tab_job_result_t) t
                     where xh = 1
                     order by creation_date desc
                     """)
        results = db.session.execute(v_sql).fetchall()
        # tmp = []
        # for item in results:
        #     item = item._asdict()
        #     tmp.append(item)
        # return jsonify({"code": 1, "msg": "【OK】"})
        # 提取结果（因为返回的是元组列表）
        compare_task_names_data = [{"comp_task_name": item[0]} for item in results]  # 提取元组中的第一个元素
        return jsonify({"code": 0, "msg": "", "count": len(compare_task_names_data), "data": compare_task_names_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
